import copy
import datetime
import math
import os
import sys
import time


# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)
from itertools import groupby
from operator import itemgetter
import json
from src.utils import CommonUtils
from src.db.CcxDataCenterDb import CcxDataCenter
from src.db.FCDB import FCDB
from src.db.ProdDb import ProdDb
from src.utils.CommonUtils import cleanScreen
from src.utils.LogUtils import Logger

class CalcIndicator:
    def __init__(self, logger, localDb, dataCenterDb, fcdb):
        self.logger = logger
        self.localDb = localDb
        self.dataCenterDb = dataCenterDb
        self.fcdb = fcdb
        self.size = 1000
        self.arg = sys.argv

    def __loadProcessList(self):
        sql = """
        SELECT  year  from index_process_year
            where TYPE =1 order by YEAR  desc
        """
        result = list(self.localDb.Query(sql))
        result = [int(item['year']) for item in result]
        return result

    # 抽取区域行政区划表
    def __loadRegionList__(self):
        sql = """
            SELECT  code,name,`type`,level from regions
            where release_status =1 and ver =0 
        """
        regionCode = CommonUtils.isCommandParam(self.arg, "regionCode")
        if regionCode['exist'] == True:
            sql = sql + " and code = '" + regionCode['value'] + "'"

        adminLevel = CommonUtils.isCommandParam(self.arg, "adminLevel")
        if adminLevel['exist'] == True:
            sql = sql + " and level = '" + adminLevel['value'] + "'"

        return self.localDb.Query(sql)

    def __loadSettings__(self):
        sql = " SELECT *  from region_index_calc_settings"
        indexCode = CommonUtils.isCommandParam(self.arg, "indexCode")
        if indexCode['exist'] == True:
            sql = sql + " where code = '" + indexCode['value'] + "'"
        return self.localDb.Query(sql)

    def __getCurrentYxzwSum__(self, data, year, baseData):
        if data is None:
            return None
        try:
            # 辖区内所有区域的有息债务余额
            result = list(filter(lambda x: x['year'] == str(year), data))[0]
            ret = {"yxzwSum1": result['xiaxiaquxian']}
            baseData.update(ret)
        except Exception as e:
            print(e)

    def __loadCurrentLevel__(self, regionCode: str, years: list) -> dict:
        sql = """
            SELECT
                a.`code` AS region_id,
                a.`year`,
                b.target_code AS 'code',
                b.target_value AS 'value',
                r.actual_father_code,
                c.third_target_name as 'name'
                 , sum(b.target_value) as 'xiaxiaquxian'
                FROM
                regions r
                left join regions_annual_data a on r.code =a.code  
                LEFT JOIN regions_annual_data_detail b ON a.id = b.annual_data_id
                left join regions_annual_data_code c on c.third_target_code=b.target_code
                WHERE
                a.year in %s
                and r.actual_father_code  = %s
                and a.release_status=1 
                and a.ver=0 
                and r.release_status =1
                and r.ver =0 
                and b.target_code='QY030302'
                GROUP BY a.year
            
        """
        return self.dataCenterDb.Query(sql, (years, regionCode))

    # 加载区域年度数据
    def __loadRegionAnnaData__(self, regionCode: str, years: list) -> dict:

        minYear = min(years)
        newYears = copy.deepcopy(years)
        newYears.append(minYear - 1)
        sql = """
           SELECT
                a.`code` AS region_id,
                a.`year`,
                b.target_code AS 'code',
                b.target_value AS 'value',
                c.third_target_name as 'name'
                FROM
                regions_annual_data a
                LEFT JOIN regions_annual_data_detail b ON a.id = b.annual_data_id
                left join regions_annual_data_code c on c.third_target_code=b.target_code
                WHERE
                a.year in %s and a.code = %s and a.release_status=1 and a.ver=0
                GROUP BY a.year,a.code,b.target_code
        """
        reportData = self.dataCenterDb.Query(sql, (newYears, regionCode))
        if len(reportData) == 0:
            return None
        reportData.sort(key=itemgetter("year"))
        return {k: {dd["code"]: dd["value"] for dd in d} for k, d in
                groupby(reportData, key=itemgetter('year'))}

    def convertFloat(self, str):
        if str is None or str == '':
            return None
        try:
            return float(str)
        except Exception as e:
            return None

    def __getCurrentReportData__(self, dicReportData: dict, year: str, baseData: dict):
        if dicReportData is None:
            baseData.update({"isExistReport": 0})
            return
        baseData.update({"isExistReport": 1})
        currentYear = {}
        t0 = {}
        t1 = {}
        if str(year) in dicReportData.keys():
            t0Data = dicReportData[str(year)];
            currentYear = {k: self.convertFloat(v) for k, v in t0Data.items()}
            t0 = {k + "_T0": self.convertFloat(v) for k, v in t0Data.items()}
        if str(int(year) - 1) in dicReportData.keys():
            t1 = dicReportData[str(int(year) - 1)]
            t1 = {k + "_T1": self.convertFloat(v) for k, v in t1.items()}
        currentYear.update(t0)
        currentYear.update(t1)
        baseData.update(currentYear)
        return baseData

    def __deleteOldData__(self, regionCode: str, year: int, settings: list):
        sql = """
            DELETE FROM region_index
            WHERE region_code= %s and year =%s and index_code in %s
        """
        setting = list({d['code'] for d in settings})
        self.localDb.ExecNoneQuery(sql, (regionCode, year, setting))

    def __save__(self, updateDatas: list):
        sql = """
        INSERT INTO region_index
        (id,region_code, `year`, index_code, index_value,create_time)
        VALUES(null, %s, %s, %s,%s,CURRENT_TIMESTAMP);
        """
        self.localDb.ExecMany(sql, updateDatas)

    def __calc__(self, settings, currentReportData, year, regionCode):
        result = []
        for setting in settings:
            formula = setting["formula"]
            code = setting['code'];
            item = []
            item.append(regionCode)
            item.append(year)
            item.append(code)
            calResult = None
            try:
                calResult = eval(formula, currentReportData)
                if code == 'QY010108':
                    calResult = calResult
                else:
                    calResult = None if calResult is None else "%.2f" % calResult
            except Exception as e:
                print("指标：", setting['code'], "公式：", formula, "原因", e)
            finally:
                item.append(calResult)
                result.append(item)
                # print(result)
        return result

    def run(self):
        # 加载设置
        settings = self.__loadSettings__()

        if len(settings) == 0:
            print("公式配置为空")
            sys.exit()

        # 加载企业列表
        regionList = self.__loadRegionList__()

        if len(regionList) == 0:
            print("区域列表为空")
            sys.exit()

        yearByCommand = CommonUtils.isCommandParam(self.arg, "year")
        if yearByCommand['exist']:
            years = list({int(dd) for dd in yearByCommand["value"].split(",")})
        else:
            years = self.__loadProcessList()

        for region in regionList:
            regionCode = region["code"]
            regionName = region["name"]
            level = int(region["level"])
            # 跑批基础数据集
            # 区域年度数据为必抽数据
            dicReports = self.__loadRegionAnnaData__(regionCode, years)
            # 如果是市级
            xiaYxzwSum = None
            if level == 2:
                xiaYxzwSum = self.__loadCurrentLevel__(regionCode, years)
            for year in years:
                baseData = {}
                baseData.update({"level": level})
                print("开始运算:", regionName, " ", year, "年 ,区域CODE:", regionCode)
                # 组装财报数据
                self.__getCurrentReportData__(dicReportData=dicReports, year=year, baseData=baseData)
                # 组装下辖区县城投有息债务余额总和
                self.__getCurrentYxzwSum__(xiaYxzwSum, year, baseData)
                calcResult = self.__calc__(settings, baseData, year, regionCode)
                self.__deleteOldData__(regionCode=regionCode, year=year, settings=settings)
                self.__save__(calcResult)


def __main__():
    logger = Logger()

    t = time.perf_counter()
    logger.info("指标计算 0.0.1")

    localDb = ProdDb(logSql=False, autocommit=True)
    dataCenterDb = CcxDataCenter(logSql=False, autocommit=True)
    fcdb = FCDB(logSql=False, autocommit=True)

    cleanScreen()

    sync = CalcIndicator(logger=logger, localDb=localDb, dataCenterDb=dataCenterDb, fcdb=fcdb)
    sync.run()

    cast = (time.perf_counter() - t)
    m = math.floor(cast / 60)
    s = cast - m * 60
    logger.info('指标计算结束 %s 总耗时: %d分%.3f秒' % (datetime.datetime.now(), m, s))


# 程序入口
if __name__ == "__main__":
    __main__()
